﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Diagnostics;
using VP.Tools;

namespace crm.model
{
    public class Conexao
    {
        public OdbcConnection Connection { get; set; }
        public List<OdbcParameter> colParams { get; set; }


        public OdbcDataAdapter Adapter;
        public OdbcCommand _ComandoSql;

        public Conexao()
        {
            Connection = new OdbcConnection("Dsn=PostgreSQL35W");
            colParams = new List<OdbcParameter>();
        }

        public void AbrirConexao()
        {
            Connection.Open();

        }

        public void FecharConexao()
        {
            Connection.Close();
            Trace.WriteLine("Estado: " + Connection.State.ToString());
            Trace.WriteLine("-------");
            Trace.WriteLine("");
        }

        public bool Executar(string query, List<OdbcParameter> colParams)
        {
            var controlador = false;
            DataTable dt = new DataTable();
            try
            {

                AbrirConexao();

                OdbcCommand command = new OdbcCommand(query, Connection);
                command.CommandText = query;
                command.Parameters.AddRange(colParams.ToArray());
                //if (colParams.Count > 0)
                //    foreach (OdbcParameter obj in colParams)
                //        command.Parameters.Add(obj); 
                Trace.WriteLine(command.CommandText);
                OdbcDataReader reader = command.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                Trace.WriteLine("Linhas Selecionadas: " + reader.RecordsAffected);
                controlador = true;
            }
            catch (Exception e)
            {
                controlador = false;
                Trace.Write(e.Message);
            }
            finally
            {
                FecharConexao();
            }
            return controlador;
        }

        public DataTable ExecutarConsulta(string query, List<OdbcParameter> colParams)
        {
            DataTable dt = new DataTable();
            try
            {
                AbrirConexao();

                OdbcCommand command = new OdbcCommand(query, Connection);
                command.CommandText = query;
                command.Parameters.AddRange(colParams.ToArray());
                //if (colParams.Count > 0)
                //    foreach (OdbcParameter obj in colParams)
                //        command.Parameters.Add(obj); 
                Trace.WriteLine(command.CommandText);
                OdbcDataReader reader = command.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                Trace.WriteLine("Linhas Selecionadas: " + reader.RecordsAffected);
            }
            catch (Exception e)
            {
                Register.Error(e);
                Trace.Write(e.Message);
            }
            finally
            {
                FecharConexao();
            }
            return dt;
        }

       
        public DataTable ExecutarConsulta(string query)
        {
            DataTable dt = new DataTable();
            try
            {
                AbrirConexao();
                OdbcCommand command = new OdbcCommand(query, Connection);
                OdbcDataReader reader = command.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                Trace.WriteLine("Linhas Selecionadas: " + reader.RecordsAffected);
            }
            catch (Exception e)
            {
                Trace.Write(e.Message);
            }
            finally
            {
                FecharConexao();
            }
            return dt;
        }

        public DataTable Selecionar(string pStrSelect)
        {
            //AbrirConexao(pStrSelect);
            
            Adapter = new OdbcDataAdapter();
            _ComandoSql = new OdbcCommand("", Connection);
            Adapter.SelectCommand = _ComandoSql;
            DataTable dtbTabela = new DataTable();

            _ComandoSql.Parameters.Clear();

            Adapter.SelectCommand.CommandText = pStrSelect;

            Adapter.Fill(dtbTabela);
            return dtbTabela;
        }
    }

}





